import re
import pymysql
from spider import spider, FilmTop, Lagou, JD, Biquge
import reconfig

def getTableName(url):
    if url == 'http://www.xbiquge.la/paihangbang/':
        return 'xbiquge'
    else:
        table = re.findall(r'www.(.*?).com', url)
        tablename = table[0]
        return tablename


def getTableLength(list):
    length = 0
    for i in list[0].keys():
        length += 1
    return length


def create(conn, table, dicts, num):
    cursor = conn.cursor()
    sql = "DROP TABLE IF EXISTS %s"
    data = (table)
    cursor.execute(sql%data)
    sql = """CREATE TABLE %s (
                 %s  INT(4) PRIMARY KEY ,
                 %s  CHAR(50)"""
    for i in range(num-2):
        sql += """
        , %s  CHAR(50)"""
    sql += """)"""
    data = [table]
    for key in dicts.keys():
        data.append(key)
    data = tuple(data)
    cursor.execute(sql%data)
    #return table



def createuser(conn):
    cursor = conn.cursor()
    sql = """CREATE TABLE users (
             username  CHAR(20) PRIMARY KEY,
             password  CHAR(50))"""
    cursor.execute(sql)
    table = 'users'
    return table



def insert(lists,table,num,conn):
    cursor = conn.cursor()
    for list in lists:
        sql = "insert into %s values("
        for i in range(num):
            if i == 0:
                sql += "'%s'"
            else:
                sql += ",'%s'"
        sql += ")"
        data = [table]
        for value in list.values():
            data.append(value)
        data = tuple(data)
        try:
            cursor.execute(sql % data)
            conn.commit()
        except:
            conn.rollback()



def getDataFromSQL(conn,table):
    cursor = conn.cursor()
    sql = "select * from %s"
    data = (table)
    data_list = []
    try:
        # 执行SQL语句
        cursor.execute(sql%data)
        # 获取所有记录列表
        results = cursor.fetchall()
        for row in results:
            data_list.append(row)
        #print(data_list)  # 打印数据列表
    except:
        print("Error: unable to fetch data")
    return data_list



def login(username, password,conn):
    cursor = conn.cursor()
    sql = "select * from users where username='%s'and password='%s'"
    data = (username, password)
    cursor.execute(sql % data)
    num = cursor.rowcount
    return num



def update(dicts,new_dicts,table,conn):
    '''
    :param dicts: 要修改的行字典
    :param new_dicts: 要修改的内容字典
    :param table: 表名
    :param conn: 连接
    '''
    cursor = conn.cursor()
    data = [table]
    for key in new_dicts.keys():
        sql = "UPDATE %s SET "
        sql += key +" = '%s' WHERE "
        data.append(new_dicts[key])
        i = 0;
        for key in dicts.keys():
            if i == 0:
                sql += key + ' = ' + "'%s'"
            else:
                sql += ' and '+ key + ' = ' + "'%s'"
            data.append(dicts[key])
            i += 1
        data = tuple(data)
        try:
            cursor.execute(sql%data)
            conn.commit()
        except:
            conn.rollback()



def delete(key, vals, table, conn):
    cursor = conn.cursor()
    sql = "DELETE FROM %s WHERE "
    data = [table]
    i = 0;
    for val in vals:
        if i == 0:
            sql += key + ' = ' + "'%s'"
        else:
            sql += ' or ' + key + ' = ' + "'%s'"
        data.append(val)
        i += 1
    data = tuple(data)
    try:
        #print(sql%data)
        cursor.execute(sql % data)
        conn.commit()
        #print('delete successfully!')
        return 0
    except:
        conn.rollback()


def getData(url):
    conn = reconfig.getconnect()
    webdrv = spider.VisitWeb(url)  # https://www.lagou.com/  # https://maoyan.com/board/4
    webdrv.set_option('C:\Program Files (x86)\Google\Chrome\Application')
    list = webdrv.visit_web()
    #print(list)
    webdrv.driver.quit()
    create(conn, getTableName(url), list[0], getTableLength(list))
    insert(list, getTableName(url), getTableLength(list), conn)
    res = getDataFromSQL(conn, getTableName(url))
    return res